package viv.karaoke.book;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class mySQLiteHelper extends SQLiteOpenHelper {

  public static final String TABLE_SONG = "song";
  public static final String SONG_ID = "songId";
  public static final String SONG_NAME = "name";
  public static final String SONG_ARTIST = "artist";
  public static final String SONG_FIRST_SENTENCE = "firstSentence";
  public static final String SONG_LYRIC = "lyric";

  public static final String TABLE_USER = "user";
  public static final String USER_ID = "userId";
  public static final String USER_NAME = "name";

  public static final String TABLE_FAVORITE = "favorite";
  public static final String FAVORITE_ID = "favoriteId";
  public static final String FAVORITE_USER_ID = "userId";
  public static final String FAVORITE_SONG_ID = "songId";

  public static final String VIEW_FAVORITE = "vwfavorite";
  public static final String VIEW_FAVORITE_ID = "favoriteId";
  public static final String VIEW_FAVORITE_USER_ID = "userId";
  public static final String VIEW_FAVORITE_SONG_ID = "songId";
  public static final String VIEW_FAVORITE_USER_NAME = "userName";
  public static final String VIEW_FAVORITE_SONG_NAME = "songName";
  public static final String VIEW_FAVORITE_SONG_ARTIST = "songArtist";
  
  
  private static final String DATABASE_NAME = "karaokeBook.db";
  private static final int DATABASE_VERSION = 1;

  // Database creation SQL statement
  private static final String DATABASE_CREATE = 
		  "create table "
	      + TABLE_SONG + "(" + SONG_ID
	      + " integer primary key autoincrement" 
	      + ", " + SONG_NAME  + " text not null" 
	      + ", " + SONG_ARTIST  + " text not null"
	      + ", " + SONG_FIRST_SENTENCE  + " text not null"      
	      + ", " + SONG_LYRIC  + " text not null"
	      + ") " 
	      + ";"
		  +" create table "
	      + TABLE_USER + "(" + USER_ID
	      + " integer primary key autoincrement" 
	      + ", " + USER_NAME  + " text not null"
	      + ") " 
		  +" create table "
	      + TABLE_FAVORITE + "(" + FAVORITE_ID
	      + " integer primary key autoincrement" 
	      + ", " + FAVORITE_USER_ID  + " integer not null"
	      + ", " + FAVORITE_SONG_ID  + " integer not null"	      
	      + ") "	      
	      + ";"
 		  +" create view "
	      + VIEW_FAVORITE + " AS " 
		  +	"SELECT f." + FAVORITE_ID + ", " 
	      + "f." + FAVORITE_USER_ID + ", " 
		  + "f." + FAVORITE_SONG_ID + ", "
	      + "s." + SONG_NAME + " as " + VIEW_FAVORITE_SONG_NAME + ", " 
		  + "s." + SONG_ARTIST + " as " + VIEW_FAVORITE_SONG_ARTIST + ", " 		  
	      + "u." + USER_NAME + "as " + VIEW_FAVORITE_USER_NAME 
	      + " FROM " + TABLE_FAVORITE + " f " 
	      + 	" INNER JOIN " 
	      + 		TABLE_USER + " u ON f." + FAVORITE_USER_ID + "= u." + USER_ID    
	      + 		TABLE_SONG + " s ON f." + FAVORITE_SONG_ID + "= s." + SONG_ID
	      + ";";

  public mySQLiteHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
  }

  @Override
  public void onCreate(SQLiteDatabase database) {
    database.execSQL(DATABASE_CREATE);
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    Log.w(mySQLiteHelper.class.getName(),
        "Upgrading database from version " + oldVersion + " to "
            + newVersion + ", which will destroy all old data");
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_SONG);
    onCreate(db);
  }

} 
